{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "bd5b1364",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## 一步步完成制作 Excel 日历\n",
    "<img src=\"./images/日历样式展示.png?raw=true\" width=\"100%\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b4e8194",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## 1. 三行代码出日历"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "385c8479",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# 导入日历工具包\n",
    "import calendar\n",
    "cal = calendar.TextCalendar()\n",
    "print(cal.formatyear(theyear=2024))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a745ed3",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## 2. 设置为中文，一排显示4个月"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e45c9f37",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import locale\n",
    "# 设置为中文\n",
    "locale.setlocale(locale.LC_ALL, 'zh_CN.UTF-8') \n",
    "cal = calendar.TextCalendar()\n",
    "# m=4  显示的日历为3行4列\n",
    "print(cal.formatyear(theyear=2024, m=4))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f54eccc",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## 3. 对格式进行简单的排版"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1d5f412",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "year=2024\n",
    "cal = calendar.TextCalendar()\n",
    "calendar_str = cal.formatyear(theyear=year, m=4)\n",
    "# 2024 => 2024年、周日 => 日\n",
    "calendar_str = calendar_str.replace(str(year),f\"{year}年\").replace(\"周\",\"\")\n",
    "print(calendar_str)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4ff5757",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## 4. 获取农历"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19fe8c59",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# 如果本地没有安装工具包，请执行\n",
    "!pip install -i https://mirrors.aliyun.com/pypi/simple/ xlwt sxtwl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c82e1f41",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import sxtwl\n",
    "# 获取农历信息\n",
    "lunar = sxtwl.fromSolar(2024, 2, 9) # 除夕\n",
    "lunar_year = lunar.getLunarYear()\n",
    "lunar_month = lunar.getLunarMonth()\n",
    "lunar_day = lunar.getLunarDay()\n",
    "# 查看农历的年月日\n",
    "print(\"农历：\",lunar_year,lunar_month,lunar_day)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67346f1b",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## 5.获取二十四节气、节假日"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "26b3c5be",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# 基于sxtwl，封装好了一个工具类\n",
    "from  calendar_utils import get_calendar_info_from_solar\n",
    "\n",
    "print(\"农历的中文\", get_calendar_info_from_solar(2024, 3, 3))  # 0、农历的中文名称，比如：初一、十五\n",
    "print(\"二十四节气\", get_calendar_info_from_solar(2024, 3, 20)) # 1、二十四节气，比如：立春、夏至\n",
    "print(\"农历节假日\", get_calendar_info_from_solar(2024, 6, 10)) # 2、农历节假日，比如：端午节、中秋\n",
    "print(\"公历节假日\", get_calendar_info_from_solar(2024, 10, 1)) # 3、公历节假日，比如：妇女节、劳动节\n",
    "# 返回日历信息的格式为：(a, b, c)\n",
    "#        a: 返回的日历名称。包含农历的中文名称、二十四节气名称、农历节假日名称、公历节假日名称\n",
    "#        b: 返回的名称类型。0.农历的中文名称 1.二十四节气名称 2.农历节假日名称 3.公历节假日名称\n",
    "#        c: 返回农历年月日。 (lunar_year, lunar_month, lunar_day)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78f4f828",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## 6. 给日历加入农历、二十四节气、节假日"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4275978d",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "### 6.1 一个月的日历数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "379c3015",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "cal = calendar.TextCalendar()\n",
    "# 获取一年的日历数据 formatyear\n",
    "# calendar_str = cal.formatyear(theyear=2024)\n",
    "\n",
    "# 获取一个月的日历数据 formatmonth\n",
    "calendar_str = cal.formatmonth(theyear=2024, themonth=2)\n",
    "\n",
    "# 2024 => 2024年、周日 => 日\n",
    "calendar_str = calendar_str.replace(str(year),f\"{year}年\").replace(\"周\",\"\")\n",
    "print(calendar_str)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9cb19f1",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "### 6.2 分析日历数据结构"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3dccf5c",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "cal_list = calendar_str.split(\"\\n\") # 用“\\n 换行符”进行分割\n",
    "print(cal_list[0]) # 第1行：年月\n",
    "print(cal_list[1]) # 第2行：周\n",
    "print(\"--------------------------\")\n",
    "for i in range(2, 7): # 第3行 开始到 第7行是公历日期\n",
    "    row = cal_list[i]\n",
    "    print(row)\n",
    "    # 将空格更换为下划线，方便查看规律\n",
    "    # print(row.replace(\" \" ,\"_\"), \"len(row)=\", len(row)) # _5_ _6_ _7_ _8_ _9_ 10_ 11\n",
    "    # row 字符串格式的规律是，3个字符串为一列数据（先不管排版）\n",
    "    # print(\">>\", row[0:3], row[3:6], row[6:9], row[9:12], row[12:15], row[15:18], row[18:20])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b53a5533",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "### 6.3 带有农历日历的完整代码"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fcccc0ea",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import calendar\n",
    "cal = calendar.TextCalendar()\n",
    "# 获取一年的日历数据 formatyear\n",
    "# calendar_str = cal.formatyear(theyear=2024)\n",
    "\n",
    "# 获取一个月的日历数据 formatmonth\n",
    "calendar_str = cal.formatmonth(theyear=2024, themonth=2)\n",
    "\n",
    "# 2024 => 2024年、周日 => 日\n",
    "calendar_str = calendar_str.replace(str(year),f\"{year}年\").replace(\"周\",\"\")\n",
    "\n",
    "cal_list = calendar_str.split(\"\\n\") # 用“\\n 换行符”进行分割\n",
    "\n",
    "print(cal_list[0]) # 年月\n",
    "print(cal_list[1]) # 周\n",
    "\n",
    "print(\"--------------------------\")\n",
    "for i in range(2, 7): # 第3行开始到7行是公历日期\n",
    "    row = cal_list[i]\n",
    "    lunar_infos =[] # 存放农历、节假日的信息\n",
    "    # 循环： row[0:3] -> row[18:20]\n",
    "    for j in range(0, 21, 3):\n",
    "        d = row[j : j+3] # row[0:3] ... row[18:20]\n",
    "        print(d, end='\\t') # 公历数据之间用“\\t 制表符”分割\n",
    "        if d.strip() == '': # d 为空说明没有日期，为了对齐，农历也追加一个空\n",
    "            lunar_infos.append('')\n",
    "            continue\n",
    "        day = int(d)\n",
    "        # 根据 公历年月日获取农历、节假日名称\n",
    "        cal_info = get_calendar_info_from_solar(2024, 2, day)\n",
    "        # 只要第一个（农历、节假日名称）\n",
    "        lunar_infos.append(cal_info[0])\n",
    "    print(\"\") # 打印完一行公历后，换行\n",
    "    for lunar_info in lunar_infos:\n",
    "        print(lunar_info, end='\\t') # 农历数据之间用“\\t 制表符”分割\n",
    "    print(\"\") # 打印完一行农历后，换行    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a560ceb",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## 7. 制作 Excel 日历"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01f7b3f8",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "### 7.1 获取公历、农历的日历数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "import calendar\n",
    "\n",
    "def get_calendar_list(year, month):\n",
    "    \"\"\"\n",
    "    获取日历列表（返回日历列表包含公历和农历信息）\n",
    "    @param year: 年份\n",
    "    @param month: 月份\n",
    "    @return: 返回日历列表包含公历和农历信息 solar_list, lunar_list\n",
    "    \"\"\"\n",
    "    cal = calendar.TextCalendar()\n",
    "\n",
    "    # 获取一个月的日历数据 formatmonth\n",
    "    calendar_str = cal.formatmonth(theyear=year, themonth=month)\n",
    "\n",
    "    # 2024 => 2024年、周日 => 日\n",
    "    calendar_str = calendar_str.replace(str(year),f\"{year}年\").replace(\"周\",\"\")\n",
    "    cal_list = calendar_str.split(\"\\n\") # 用“\\n 换行符”进行分割\n",
    "\n",
    "    year_title = cal_list[0] # 年月\n",
    "    week_titles = cal_list[1] # 周\n",
    "\n",
    "    # 存放所有的公历数据\n",
    "    solar_list =[]\n",
    "    # 存放所有的农历数据\n",
    "    lunar_list =[]\n",
    "\n",
    "    for i in range(2, 7): # 第3行开始到7行是公历日期\n",
    "        row = cal_list[i]\n",
    "        lunar_infos =[] # 存放农历、节假日的信息\n",
    "        solar_infos =[] # 存放公历信息\n",
    "        # 循环： row[0:3] -> row[18:20]\n",
    "        for j in range(0, 21, 3):\n",
    "            d = row[j : j+3] # row[0:3] ... row[18:20]\n",
    "            solar_infos.append(d)\n",
    "            if d.strip() == '': # d 为空说明没有日期，为了对齐，农历也追加一个空\n",
    "                lunar_infos.append('')\n",
    "                continue\n",
    "            day = int(d)\n",
    "            # 根据 公历年月日获取农历、节假日名称\n",
    "            cal_info = get_calendar_info_from_solar(2024, 2, day)\n",
    "            # 只要第一个（农历、节假日名称）\n",
    "            lunar_infos.append(cal_info[0])\n",
    "        # 将公历、农历的日历数据放入列表，作为后面代码使用\n",
    "        solar_list.append(solar_infos)\n",
    "        lunar_list.append(lunar_infos)\n",
    "    \n",
    "    return solar_list, lunar_list"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "solar_list, lunar_list = get_calendar_list(year=2024, month=2)\n",
    "# 循环显示\n",
    "for i, r in enumerate(range(0, len(solar_list))):\n",
    "    for w in range(0, 7):\n",
    "        print(solar_list[i][w],end=\"\\t\")\n",
    "    print(\"\")\n",
    "    for w in range(0, 7):\n",
    "        print(lunar_list[i][w],end=\"\\t\")  \n",
    "    print(\"\")"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 7.2 将日历数据循环写入Excel"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "import xlwt\n",
    "from  calendar_utils import CN_WEEKS\n",
    "\n",
    "workbook = xlwt.Workbook(encoding='utf-8')\n",
    "sheet = workbook.add_sheet('2024年2月')\n",
    "# 周名称\n",
    "print(\"周名称\", CN_WEEKS)\n",
    "# 写入周标题\n",
    "for w in range(0, 7):\n",
    "    sheet.write(r=0, c=w, label=CN_WEEKS[w])\n",
    "\n",
    "# 写入日历数据\n",
    "for i, r in enumerate(range(0, len(solar_list))):\n",
    "    # 写入公历\n",
    "    for w in range(0, 7):\n",
    "        sheet.write(r=i*2+1, c=w, label=solar_list[i][w])\n",
    "    # 写入农历\n",
    "    for w in range(0, 7):\n",
    "        sheet.write(r=i*2+2, c=w, label=lunar_list[i][w])\n",
    "    \n",
    "workbook.save('./files/2024年2月日历.xls')"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1abeb9a1",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import xlwt\n",
    "from  calendar_utils import CN_WEEKS\n",
    "\n",
    "workbook = xlwt.Workbook(encoding='utf-8')\n",
    "sheet = workbook.add_sheet('2024年2月')\n",
    "# 周名称\n",
    "print(\"周名称\", CN_WEEKS)\n",
    "# 写入周标题\n",
    "for w in range(0, 7):\n",
    "    sheet.write(r=0, c=w, label=CN_WEEKS[w])\n",
    "\n",
    "# 写入日历数据\n",
    "for i, r in enumerate(range(0, len(solar_list))):\n",
    "    # 写入公历\n",
    "    for w in range(0, 7):\n",
    "        sheet.write(r=i*2+1, c=w, label=solar_list[i][w])\n",
    "    # 写入农历\n",
    "    for w in range(0, 7):\n",
    "        sheet.write(r=i*2+2, c=w, label=lunar_list[i][w])\n",
    "    \n",
    "workbook.save('./files/2024年2月日历.xls')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f974edb",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "### 7.3 美化日历（给日历添加样式）\n",
    "\n",
    "<img src=\"./images/2024_2.png?raw=true\" width=\"30%\"/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bdf3a144",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import xlwt\n",
    "from  calendar_utils import create_style_01, CN_WEEKS\n",
    "\n",
    "# 创建一个日历样式对象\n",
    "cal_style = create_style_01()\n",
    "\n",
    "workbook = xlwt.Workbook(encoding='utf-8')\n",
    "sheet = workbook.add_sheet('2024年2月')\n",
    "# 周名称\n",
    "print(\"周名称\", CN_WEEKS)\n",
    "\n",
    "# 写入周标题\n",
    "for w in range(0, 7):\n",
    "    sheet.write(r=0, c=w, label=CN_WEEKS[w], style=cal_style.week_style)\n",
    "\n",
    "# 写入日历数据\n",
    "for i, r in enumerate(range(0, len(solar_list))):\n",
    "    # 写入公历\n",
    "    for w in range(0, 7):\n",
    "        sheet.write(r=i*2+1, c=w, label=solar_list[i][w], style=cal_style.solar_style)\n",
    "    # 写入农历\n",
    "    for w in range(0, 7):\n",
    "        sheet.write(r=i*2+2, c=w, label=lunar_list[i][w], style=cal_style.lunar_style)\n",
    "    \n",
    "workbook.save('./files/2024年2月日历.xls')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95fb0ed3",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "### 7.4 进一步完善（练习要求）\n",
    "\n",
    "####  参考 01.Excel操作/ 03.Excel样式处理\n",
    "\n",
    "1. 周六、周日、节假日为红色字\n",
    "\n",
    "2. 完成一整年的日历\n",
    "\n",
    "3. 日历共用单元格的效果（单元格对角线）\n",
    "\n",
    "\n",
    "```python\n",
    "# 该样式为单元格对角线\n",
    "cal_style.shared_cell_style\n",
    "```\n",
    "\n",
    "<img src=\"./images/2024_12.png?raw=true\" width=\"30%\"/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1337be1d",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}